﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_Overseas
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_Overseas
    {

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetOverseas(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format(@"SELECT  ov.OverseasKey,
        ov.ApplicantKey,
        ov.StartDate,
        ov.EndDate,
        cty.DisplayText AS 'CountryDesc',
        dur.DisplayText AS 'DurationOfStayDesc',
        pur.DisplayText AS 'PurposeDesc',
        ov.Others,
        ov.Country,
        ov.DurationOfStay,
        ov.Purpose 
FROM    SRM_ApplicantOverseas_Tbl ov
LEFT JOIN
        SAP_Code cty
        ON  cty.CodeTypeId = 'CTY' 
        AND cty.Id = ov.Country
LEFT JOIN
        SAP_Code dur 
        ON  dur.CodeTypeId = 'DUR' 
        AND dur.Id = ov.DurationOfStay
LEFT JOIN
        SAP_Code pur 
        ON  pur.CodeTypeId = 'PUR' 
        AND pur.Id = ov.Purpose
WHERE   ov.ApplicantKey = {0};", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetOverseas(string applicantKey)


        [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
        public static void InsertOverseas(string applicantKey,
            string startDate, string endDate, string country, string durationOfStay, string purpose, string others)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT INTO SRM_ApplicantOverseas_Tbl (ApplicantKey, StartDate, EndDate, Country, DurationOfStay, Purpose, Others) VALUES (@ApplicantKey, @StartDate, @EndDate, @Country, @DurationOfStay, @Purpose, @Others);";
                cmd.Parameters.AddWithValue("@ApplicantKey", applicantKey);

                cmd.Parameters.AddWithValue("@StartDate", Help.GetValueOrDBNull(startDate));
                cmd.Parameters.AddWithValue("@EndDate", Help.GetValueOrDBNull(endDate));
                cmd.Parameters.AddWithValue("@Country", Help.GetValueOrDBNull(country));
                cmd.Parameters.AddWithValue("@DurationOfStay", Help.GetValueOrDBNull(durationOfStay));
                cmd.Parameters.AddWithValue("@Purpose", Help.GetValueOrDBNull(purpose));
                cmd.Parameters.AddWithValue("@Others", Help.GetValueOrDBNull(others));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)



        [DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
        public static void DeleteOverseas(string overseasKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM SRM_ApplicantOverseas_Tbl WHERE OverseasKey = @OverseasKey;";
                cmd.Parameters.AddWithValue("@OverseasKey", overseasKey);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)



        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateOverseas(string overseasKey,
            string startDate, string endDate, string country, string durationOfStay, string purpose, string others)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"
UPDATE  SRM_ApplicantOverseas_Tbl 
SET     StartDate = @StartDate, 
        EndDate = @EndDate, 
        Country = @Country,
        DurationOfStay = @DurationOfStay,
        Purpose = @Purpose,
        Others = @Others
WHERE   OverseasKey = @OverseasKey;";

                cmd.Parameters.AddWithValue("@OverseasKey", overseasKey);
                cmd.Parameters.AddWithValue("@StartDate", Help.GetValueOrDBNull(startDate));
                cmd.Parameters.AddWithValue("@EndDate", Help.GetValueOrDBNull(endDate));
                cmd.Parameters.AddWithValue("@Country", Help.GetValueOrDBNull(country));
                cmd.Parameters.AddWithValue("@DurationOfStay", Help.GetValueOrDBNull(durationOfStay));
                cmd.Parameters.AddWithValue("@Purpose", Help.GetValueOrDBNull(purpose));
                cmd.Parameters.AddWithValue("@Others", Help.GetValueOrDBNull(others));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void UpdateOverseas(string OverseasKey,

    }
}